"""
Case Type   : column_unique_index
Case Name   : 创建普通索引&唯一索引，对比行存表、列存表插入效率
Create At   : 2024/4/15
Owner       : @li-xin12345
Description :
    1、创建行存表，分别创建普通索引和唯一索引
    2、创建列存表，分别创建普通索引和唯一索引
    3、分别向行存表和列存表中插入相同数据，对比插入时间
    4、清理环境
Expect      :
    1、成功
    2、成功
    3、相同数据，普通索引比唯一索引插入数据效率高，行存表比列存表插入数据效率高
    4、成功
History     :
"""

import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class ColumnUniqueIndexTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.tb_name = ['t_index_0045_01', 't_index_0045_02',
                        't_index_0045_03', 't_index_0045_04']
        self.index_name = ['i_index_0045_01', 'i_index_0045_02',
                           'i_index_0045_03', 'i_index_0045_04']

    def test_column_unique_index(self):
        text = '-----step1:创建行存表，分别创建普通索引和唯一索引;expect:成功-----'
        self.log.info(text)
        sql = f'drop table if exists {self.tb_name[0]} cascade;' \
              f'drop table if exists {self.tb_name[1]} cascade;' \
              f'create table {self.tb_name[0]}(name varchar unique, ' \
              f'id int primary key);' \
              f'create table {self.tb_name[1]}(name varchar unique, ' \
              f'id int primary key);' \
              f'create index {self.index_name[0]} on ' \
              f'{self.tb_name[0]} using btree(id);' \
              f'create unique index {self.index_name[1]} on ' \
              f'{self.tb_name[1]} using btree(id);'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertEqual(res.count(self.constant.TABLE_DROP_SUCCESS), 2,
                         '执行失败：' + text)
        self.assertEqual(res.count(self.constant.TABLE_CREATE_SUCCESS), 6,
                         '执行失败：' + text)
        self.assertEqual(res.count(self.constant.CREATE_INDEX_SUCCESS_MSG), 2,
                         '执行失败：' + text)

        text = '-----step2:创建列存表，创建唯一索引;expect:成功-----'
        self.log.info(text)
        sql = f'drop table if exists {self.tb_name[2]} cascade;' \
              f'drop table if exists {self.tb_name[3]} cascade;' \
              f'create table {self.tb_name[2]}(name varchar unique, ' \
              f'id int primary key);' \
              f'create table {self.tb_name[3]}(name varchar unique, ' \
              f'id int primary key);' \
              f'create index {self.index_name[2]} on ' \
              f'{self.tb_name[2]} using btree(id);' \
              f'create unique index {self.index_name[3]} on ' \
              f'{self.tb_name[3]} using btree(id);'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertEqual(res.count(self.constant.TABLE_DROP_SUCCESS), 2,
                         '执行失败：' + text)
        self.assertEqual(res.count(self.constant.TABLE_CREATE_SUCCESS), 6,
                         '执行失败：' + text)
        self.assertEqual(res.count(self.constant.CREATE_INDEX_SUCCESS_MSG), 2,
                         '执行失败：' + text)

        text = '-----step3:分别向行存表和列存表中插入相同数据，对比插入时间;' \
               'expect:成功-----'
        self.log.info(text)
        insert_time = []
        for i in self.tb_name:
            sql = f"insert into {i} values" \
                  f"('test_'||generate_series(1, 100000), " \
                  f"generate_series(1, 100000));"
            start_time = time.time()
            res = self.pri_sh.execut_db_sql(sql)
            self.log.info(res)
            end_time = time.time()
            insert_time.append(end_time - start_time)
            self.assertIn(self.constant.INSERT_SUCCESS_MSG, res,
                          '执行失败：' + text)
        self.log.info(insert_time)
        self.assertLess(insert_time[0], insert_time[1], '执行失败：' + text)
        self.assertLess(insert_time[2], insert_time[3], '执行失败：' + text)

    def tearDown(self):
        text = '-----step4:清理环境;expect:成功-----'
        self.log.info(text)
        for i in self.tb_name:
            sql = f'drop table if exists {i} cascade;'
            res = self.pri_sh.execut_db_sql(sql)
            self.log.info(res)
            self.assertIn(self.constant.TABLE_DROP_SUCCESS, res,
                          '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
